﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.SqlClient;
using VideoClassLibrary.Model;
using System.Data.Common;
namespace VideoClassLibrary.BusinessLogicLayer
{
   public  class TrainClassModule
    {
        /// <summary>
        ///   添加培训班信息
        /// </summary>
        /// <returns></returns>
        public int AddTrainclass(V_TrainClass model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into V_trainClass(");
            strSql.Append("Create_time,update_time,SchoolID,VTC_Number,VTC_name,VTC_desc,vtc_starDate,VTC_endDate,MaxUserCount,IsPublish)");

            strSql.Append(" values (");
            strSql.Append("@Create_time,@update_time,@SchoolID,@VTC_Number,@VTC_name,@VTC_desc,@vtc_stardate,@vtc_endDate,@MaxUserCount,@IsPublish)");
            strSql.Append(";select @@IDENTITY");
            Database db = VDataBase.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
            db.AddInParameter(dbCommand, "Create_time", DbType.DateTime, model.Create_time);
            db.AddInParameter(dbCommand, "update_time", DbType.DateTime, model.update_time);
            db.AddInParameter(dbCommand, "SchoolID", DbType.Int32, model.SchoolID);
            db.AddInParameter(dbCommand, "VTC_Number", DbType.String, model.VTC_Number);
            db.AddInParameter(dbCommand, "MaxUserCount", DbType.Int32, model.MaxUserCount);
            db.AddInParameter(dbCommand, "VTC_name", DbType.AnsiString, model.VTC_name);
            db.AddInParameter(dbCommand, "VTC_desc", DbType.AnsiString, model.VTC_desc);
            db.AddInParameter(dbCommand, "vtc_stardate", DbType.DateTime, model.VTC_StartDate);
            db.AddInParameter(dbCommand, "vtc_enddate", DbType.DateTime, model.VTC_EndDate);
            db.AddInParameter(dbCommand, "IsPublish", DbType.Boolean, model.IsPublish);
            
            int result = db.ExecuteNonQuery(dbCommand);
            if (result > 0)
            { return 1; }
            else
            { return -1; }

        }

        /// <summary>
        ///   查询培训班信息
        /// </summary>
        /// <returns></returns>
        public DataSet GetTrainclassListByWhere(int schoolId,string whereSql)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select a.ID,a.Create_time,a.update_time,a.SchoolID,VTC_Number,VTC_name,VTC_desc,vtc_starDate,vtc_endDate,b.vs_name,MaxUserCount,a.IsPublish ");
            strSql.Append(" FROM V_trainClass a join  v_school  b on b.id=a.SchoolID  where a.SchoolID= " + schoolId );

            if (whereSql != "")
            {
                strSql.Append(whereSql);
            }
            strSql.AppendLine(" order by Create_time desc");
            Database db = VDataBase.CreateDatabase();

            return db.ExecuteDataSet(CommandType.Text, strSql.ToString());
        }
       
        /// <summary>
        ///  根据ID获取培训班实体
        /// </summary>
        /// <returns></returns>
        public V_TrainClass GetTrainClassModelById(int TCId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT  *  FROM V_trainClass ");
            strSql.Append(" where ID=@ID ");
            Database db = VDataBase.CreateDatabase();
            DbCommand command = db.GetSqlStringCommand(strSql.ToString());
            db.AddInParameter(command, "ID", DbType.Int32, TCId);
            DataSet dsResult = db.ExecuteDataSet(command);
            V_TrainClass model = null;

            if (dsResult != null && dsResult.Tables.Count > 0 && dsResult.Tables[0].Rows.Count > 0)
            {
                DataRow dr = dsResult.Tables[0].Rows[0];
                model = new V_TrainClass();
                model.Create_time = dr["Create_time"] == DBNull.Value ? System.DateTime.Now : System.DateTime.Parse(dr["create_time"].ToString());
                model.ID = int.Parse(dr["ID"].ToString());
                model.SchoolID = int.Parse(dr["SchoolID"].ToString());
                model.VTC_name = dr["VTC_name"].ToString();
                model.VTC_desc = dr["VTC_desc"].ToString();
                model.VTC_Number = dr["VTC_Number"].ToString();
                model.MaxUserCount =int.Parse(dr["MaxUserCount"].ToString());
                model.VTC_StartDate =dr["VTC_starDate"]==DBNull.Value?DateTime.Now: DateTime.Parse(dr["VTC_starDate"].ToString());
                model.VTC_EndDate = dr["VTC_starDate"] == DBNull.Value ? DateTime.Now : DateTime.Parse(dr["VTC_starDate"].ToString());
                model.IsPublish = Convert.ToInt32(dr["IsPublish"]);
                dsResult = null;
            }
            return model;
        }

        /// <summary>
        ///  修改培训班信息
        /// </summary>
        /// <returns></returns> 
        public int UpdateTrainClass(V_TrainClass model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update V_trainClass set ");
            strSql.Append("update_time=@update_time,");
            strSql.Append("SchoolID=@SchoolID,");
            strSql.Append("VTC_Number=@VTC_Number,");
            strSql.Append("MaxUserCount=@MaxUserCount,");
            strSql.Append("VTC_name=@VTC_name,");
            strSql.Append("VTC_desc=@VTC_desc,");
            strSql.Append("IsPublish=@IsPublish");
            strSql.Append(" where ID=@ID ");
            Database db = VDataBase.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
            db.AddInParameter(dbCommand, "ID", DbType.Int32, model.ID);
            db.AddInParameter(dbCommand, "update_time", DbType.DateTime, model.update_time);
            db.AddInParameter(dbCommand, "SchoolID", DbType.Int32, model.SchoolID);
            db.AddInParameter(dbCommand, "VTC_Number", DbType.AnsiString, model.VTC_Number);
            db.AddInParameter(dbCommand, "MaxUserCount", DbType.Int32, model.MaxUserCount);
            db.AddInParameter(dbCommand, "VTC_name", DbType.AnsiString, model.VTC_name);
            db.AddInParameter(dbCommand, "VTC_desc", DbType.AnsiString, model.VTC_desc);
            db.AddInParameter(dbCommand, "IsPublish",DbType.Int32,model.IsPublish);
            int result = db.ExecuteNonQuery(dbCommand);
            if (result > 0)
            { return 1; }
            else
            { return -1; }

        }
        /// <summary>
        /// 根据培训班ID获取名称
        /// </summary>
        /// <returns></returns> 
        public string GetTrainClassNameByID(int tcid)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select VTC_name FROM V_trainClass where ID=@tcid");
            Database db = VDataBase.CreateDatabase();
            DbCommand command = db.GetSqlStringCommand(strSql.ToString());
            db.AddInParameter(command, "tcid", DbType.Int32, tcid);
            DataSet dsResult = db.ExecuteDataSet(command);
            string TCName = null;
            if (dsResult != null && dsResult.Tables.Count > 0 && dsResult.Tables[0].Rows.Count > 0)
            {
                DataRow dr = dsResult.Tables[0].Rows[0];
                TCName = dr["VTC_name"].ToString();
            }
            return TCName;
        }
        /// <summary>
        /// 根据学校ID和培训班名获取培训班信息
        /// </summary>
        /// <returns></returns> 
        public DataSet QueryByTrainAllBySchoolId(int id, string train)
        {

            StringBuilder strSql=new StringBuilder();
            strSql.Append("select a.id,a.create_time, a.update_time,a.vtc_name,a.vtc_desc,b.vs_name,VTC_Number from V_trainClass as a join v_school as b on b.id=@id where a.VTC_name like '%" + train + "%'");
            Database db = VDataBase.CreateDatabase();
            DbCommand command = db.GetSqlStringCommand(strSql.ToString());
            db.AddInParameter(command, "id", DbType.Int32, id);
            //db.AddInParameter(command, "train", DbType.String, train);
            return db.ExecuteDataSet(command);
        }

        /// <summary>
        /// 获取用户所有的班级信息
        /// </summary>
        /// <param name="schoolId"></param>
        /// <param name="userId"></param>
        /// <param name="trainingClassId"></param>
        /// <returns></returns>
        public DataSet GetUserTrainingClassList(int schoolId,int userId,int trainingClassId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" SELECT  	a.UserID,a.SchoolID,TrainClassID ,b.VTC_name,b.VTC_Number,b.VTC_desc,b.VTC_starDate,b.VTC_endDate,b.MaxUserCount ");
           strSql.Append(" FROM V_courselistDest  a join V_trainClass b on a.TrainClassID=b.ID");
           strSql.Append("   where a.UserID="+userId+"  and a.SchoolID="+schoolId);

           if (trainingClassId > 0)
           {
               strSql.Append("  and a.TrainClassID="+trainingClassId);
           }
            Database db = VDataBase.CreateDatabase();
            return db.ExecuteDataSet(CommandType.Text, strSql.ToString());
        }
        /// <summary>
        /// 判断是否可以添加
        /// </summary>
        public bool IsTrainClassNum(string Num)
        {
            string sql = "select VTC_Number from V_trainClass where VTC_Number=@num ";
            Database db = VDataBase.CreateDatabase();
            DbCommand command = db.GetSqlStringCommand(sql);
            db.AddInParameter(command, "num", DbType.String, Num);
            DataSet dsResult = db.ExecuteDataSet(command);
            if (dsResult != null && dsResult.Tables.Count > 0 && dsResult.Tables[0].Rows.Count > 0)
            {
                DataRow dr = dsResult.Tables[0].Rows[0];
                string VS_names = dr["VTC_Number"].ToString();
                if (VS_names == "" || VS_names == null)
                {
                    return true;

                }
                else
                { return false; }
            }
            else
            { return true; }
        }

        /// <summary>
        /// 判断是否可以添加
        /// </summary>
        public bool IsTrainClassNumBySchoolId(int schoolId,string num)
        {
            string sql = "select VTC_Number from V_trainClass where VTC_Number=@num and SchoolID=@SchoolID";
            Database db = VDataBase.CreateDatabase();
            DbCommand command = db.GetSqlStringCommand(sql);
            db.AddInParameter(command, "num", DbType.String, num);
            db.AddInParameter(command, "SchoolID", DbType.Int32, schoolId);
            DataSet dsResult = db.ExecuteDataSet(command);
            if (dsResult != null && dsResult.Tables.Count > 0 && dsResult.Tables[0].Rows.Count > 0)
            {
                DataRow dr = dsResult.Tables[0].Rows[0];
                string VS_names = dr["VTC_Number"].ToString();
                if (VS_names == "" || VS_names == null)
                {
                    return true;

                }
                else
                { return false; }
            }
            else
            { return true; }
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public void TrainDelete(int trainClassID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from V_trainClass ");
            strSql.Append(" where ID=@ID ");
            Database db = VDataBase.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
            db.AddInParameter(dbCommand, "ID", DbType.Int32, trainClassID);
            db.ExecuteNonQuery(dbCommand);

        }
    }
}
